package com.bright.ghj.overt.manager.impl;

import com.bright.ghj.common.pojo.dto.PageDTO;
import com.bright.ghj.common.util.StringUtil;
import com.bright.ghj.common.enums.FieldType;
import com.bright.ghj.overt.enums.PublicType;
import com.bright.ghj.overt.manager.BaseDataManager;
import com.bright.ghj.overt.manager.EncryptManager;
import com.bright.ghj.overt.pojo.dto.FileItemDTO;
import com.bright.ghj.overt.pojo.dto.FileListDTO;
import com.bright.ghj.overt.pojo.dto.TableDataDTO;
import com.bright.ghj.overt.pojo.query.TableDataQuery;
import com.bright.ghj.overt.pojo.vo.SaveResultVO;
import com.bright.ghj.overt.util.EntityManagerUtil;
import com.bright.ghj.common.util.ListUtil;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.util.HtmlUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


@Component
@RequiredArgsConstructor
public class BaseDataManagerImpl implements BaseDataManager {

    private final EncryptManager encryptManager;

    private final ObjectMapper objectMapper;

    private final JdbcTemplate jdbcTemplatePrimary;

    @PersistenceContext
    private final EntityManager entityManager;


    @Override
    public PageDTO<Map<String, Object>> pageTableData(TableDataQuery tableDataQuery, FileListDTO fileList, List<FileItemDTO> fileItems) {

        // 获取字段
        String fieldSql = getListTableDataFieldSql(fileItems, fileList.getOrderField(), true);

        String queryCountSql = "select count(*) from \"" + fileList.getFileName() + "\" " + getListTableDataSubSql(fileList);
        String querySql = StringUtil.replace(queryCountSql, "count(*)", fieldSql)
                + " order by " + fileList.getOrderField();

        // 查总条数和页数
//        Query countNativeQuery = getListTableDataNativeQuery(tableDataQuery, fileList, queryCountSql);
//        Long total = Long.valueOf(countNativeQuery.getSingleResult().toString());
        Map<String, Object> params = getListTableDataParameters(fileList, tableDataQuery);
        Long total = EntityManagerUtil.queryForCount(entityManager, queryCountSql, params);
        int pageCount = (int) Math.ceil((double) total / tableDataQuery.getPageSize());
        List<Map<String, Object>> resultList;

        if (total > 0) {

            // 具体数据
            resultList = EntityManagerUtil.queryForMapList(entityManager, querySql, params, tableDataQuery);
            // 特殊处理 如解密
            for (Map<String, Object> eachLineMap : resultList) {
                for (FileItemDTO fileItem : fileItems) {
                    String key = fileItem.getFieldName().toUpperCase();
                    Object fieldValue = eachLineMap.get(key);
                    eachLineMap.put(key, readDataAfterProcess(fieldValue, fileItem));
                }
            }
        } else {
            resultList = new ArrayList<>();
        }

        return PageDTO.of(total, pageCount, resultList);
    }

    @Override
    public List<Map<String, Object>> listTableData(TableDataQuery tableDataQuery, FileListDTO fileList, List<FileItemDTO> fileItems) {
//        String fieldSql = StringUtil.join(getListTableDataFieldSql(fileItems, fileList.getOrderField()), ",");
        String fieldSql = getListTableDataFieldSql(fileItems, fileList.getOrderField(), true);

        String queryCountSql = "select count(*) from \"" + fileList.getFileName() + "\" " + getListTableDataSubSql(fileList);
        String querySql = StringUtil.replace(queryCountSql, "count(*)", fieldSql)
                + " order by " + fileList.getOrderField();

//        Query nativeQuery = getListTableDataNativeQuery(tableDataQuery, fileList, querySql);
//        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
//        return nativeQuery.getResultList();
        Map<String, Object> params = getListTableDataParameters(fileList, tableDataQuery);
        List<Map<String, Object>> resultList = EntityManagerUtil.queryForMapList(entityManager, querySql, params);
        // 特殊处理 如解密
        for (Map<String, Object> eachLineMap : resultList) {
            for (FileItemDTO fileItem : fileItems) {
                String key = fileItem.getFieldName().toUpperCase();
                Object fieldValue = eachLineMap.get(key);
                eachLineMap.put(key, readDataAfterProcess(fieldValue, fileItem));
            }
        }
        return resultList;
    }

//    @Override
//    public SaveResultVO saveTableDataOld(TableDataDTO tableDataDTO, FileListDTO fileList, final List<FileItemDTO> fileItems) throws JsonProcessingException {
//        SaveResultVO saveResultVO = new SaveResultVO();
//        List<String> errorInfos = new ArrayList<>();
//
//        // 数据保存方法
//        final Integer years = tableDataDTO.getYears(), months = tableDataDTO.getMonths(), days = tableDataDTO.getDays();
//        final String ztId = tableDataDTO.getZtId();
//        final String tableName = tableDataDTO.getTableName();
//        final String sno = tableDataDTO.getSno();
//        String sqlTableName = "\"" + fileList.getFileName() + "\"";
//        // tableData的格式详见TableDataDTO类  是Map<String, List<Map<String, Object>>>  key=表名 value=表数据
//        Map<String, List<Map<String, Object>>> tableDataJSONObj = objectMapper.readValue(tableDataDTO.getTableData(), Map.class);
//        List<Map<String, Object>> tableDataArray = tableDataJSONObj.get(tableName);
//        PublicType publicType = null;
//
//        if (tableDataArray == null) {
//            errorInfos.add("tableData获取不到对应的表数据，保存失败：" + tableName);
//        } else {
//
//            // 获取公开类型
//            publicType = fileList.getPublicType();
//
//            // 检验数据类型是否合规
//            errorInfos.addAll(checkDataValid(tableDataArray, fileList, fileItems));
//        }
//        if (errorInfos.size() > 0) {
//            saveResultVO.setSuccess(false);
//            saveResultVO.setInfoList(errorInfos);
//            return saveResultVO;
//        }
//
//        // 获取已有的数据的id主键列
//        TableDataQuery tableDataQuery = new TableDataQuery();
//        tableDataQuery.setYears(years);
//        tableDataQuery.setMonths(months);
//        tableDataQuery.setDays(days);
//        tableDataQuery.setZtId(ztId);
//        tableDataQuery.setTableName(tableName);
//        tableDataQuery.setSno(sno);
//        List<Map<String, Object>> tableDataMaps = listTableData(tableDataQuery, fileList, fileItems);
//        // 已存在数据的id列表
//        List<Integer> existIdList = ListUtil.toSingleList(tableDataMaps, map -> (Integer) map.get("ID"));
//        // 准备保存数据的id列表
//        List<Integer> saveIdList = ListUtil.toSingleList(tableDataArray, object -> (Integer) object.get("ID"));
//
//        // 获取新增、修改、删除数据
//        final List<Map<String, Object>> addArray = new ArrayList<>(), updateArray = new ArrayList<>();
//        List<Integer> deleteIdArray = new ArrayList<>();
//
//
//        for (Map<String, Object> jsonObject : tableDataArray) {
//            if (jsonObject.get("ID") == null) {
//                throw new RuntimeException("tableData数据行缺少ID字段，若新增则传0");
//            }
//            int id = (Integer) jsonObject.get("ID");
//
//            if (existIdList.contains(id)) {
//                updateArray.add(jsonObject);
//            } else {
//                addArray.add(jsonObject);
//            }
//        }
//
//        for (Integer existId : existIdList) {
//            if (!saveIdList.contains(existId)) {
//                deleteIdArray.add(existId);
//            }
//        }
//
//        final int fileItemSize = fileItems.size();
//
//        insertTableDataByYearMonthZth(fileItems, years, months, days, ztId, sno, sqlTableName, publicType, addArray, fileItemSize);
//        List<String> valueSqlList;
//
//        // update
//        StringBuilder updateSqlBuffer = new StringBuilder();
//        updateSqlBuffer.append("UPDATE ").append(sqlTableName).append(" SET ");
//        // 拼接update字段
//        valueSqlList = ListUtil.toSingleList(fileItems, fileItem -> fileItem.getFieldName() + "=?");
//        updateSqlBuffer.append(StringUtil.join(valueSqlList, ","));
//        updateSqlBuffer.append(" WHERE ").append(fileList.getOrderField()).append("=?");
//
//        jdbcTemplatePrimary.batchUpdate(updateSqlBuffer.toString(), new BatchPreparedStatementSetter() {
//            @Override
//            public void setValues(PreparedStatement ps, int i) throws SQLException {
//                Map<String, Object> jsonObject = updateArray.get(i);
//                for (int j = 0; j < fileItemSize; j++) {
//                    FileItemDTO fileItem = fileItems.get(j);
//                    String fieldName = fileItem.getFieldName().toUpperCase();
//                    ps.setObject(j+1, getDataAfterProcess(jsonObject.get(fieldName), fileItem));
//                }
//                // where id=?
//                ps.setInt(fileItemSize + 1, (Integer) jsonObject.get("ID"));
//            }
//
//            @Override
//            public int getBatchSize() {
//                return updateArray.size();
//            }
//        });
//
//        // delete
//        jdbcTemplatePrimary.batchUpdate("DELETE FROM " + sqlTableName + " WHERE " + fileList.getOrderField() + "=?", new BatchPreparedStatementSetter() {
//            @Override
//            public void setValues(PreparedStatement ps, int i) throws SQLException {
//                ps.setInt(1, deleteIdArray.get(i));
//            }
//
//            @Override
//            public int getBatchSize() {
//                return deleteIdArray.size();
//            }
//        });
//
//        // 返回保存成功
//        saveResultVO.setSuccess(true);
//        return saveResultVO;
//    }

    // 由于前端允许调换行之间的顺序 目前没有字段控制排序 改成先删除数据再全部插入
    @Transactional
    @Override
    public SaveResultVO saveTableData(TableDataDTO tableDataDTO, FileListDTO fileList, final List<FileItemDTO> fileItems) throws JsonProcessingException {
        SaveResultVO saveResultVO = new SaveResultVO();
        List<String> errorInfos = new ArrayList<>();

        // 数据保存方法
        final Integer years = tableDataDTO.getYears(), months = tableDataDTO.getMonths(), days = tableDataDTO.getDays();
        final String ztId = tableDataDTO.getZtId();
        final String tableName = tableDataDTO.getTableName();
        final String sno = tableDataDTO.getSno();
        String sqlTableName = "\"" + fileList.getFileName() + "\"";
        // tableData的格式详见TableDataDTO类  是Map<String, List<Map<String, Object>>>  key=表名 value=表数据
        Map<String, List<Map<String, Object>>> tableDataJSONObj = objectMapper.readValue(tableDataDTO.getTableData(), Map.class);
        List<Map<String, Object>> tableDataArray = tableDataJSONObj.get(tableName);
        PublicType publicType = null;

        if (tableDataArray == null) {
            errorInfos.add("tableData获取不到对应的表数据，保存失败：" + tableName);
        } else {

            // 获取公开类型
            publicType = fileList.getPublicType();

            // 检验数据类型是否合规
            errorInfos.addAll(checkDataValid(tableDataArray, fileList, fileItems));
        }
        if (errorInfos.size() > 0) {
            saveResultVO.setSuccess(false);
            saveResultVO.setInfoList(errorInfos);
            return saveResultVO;
        }


        // 删除旧的数据
        TableDataQuery tableDataQuery = new TableDataQuery();
        tableDataQuery.setYears(years);
        tableDataQuery.setMonths(months);
        tableDataQuery.setDays(days);
        tableDataQuery.setZtId(ztId);
        tableDataQuery.setTableName(tableName);
        tableDataQuery.setSno(sno);
        String deleteSQL = "delete from \"" + tableName + "\" " + getListTableDataSubSql(fileList);
        Map<String, Object> params = getListTableDataParameters(fileList, tableDataQuery);
        EntityManagerUtil.update(entityManager, deleteSQL, params);

        final int fileItemSize = fileItems.size();
        // 插入数据
        insertTableDataByYearMonthZth(fileItems, years, months, days, ztId, sno, sqlTableName, publicType, tableDataArray, fileItemSize);

        // 返回保存成功
        saveResultVO.setSuccess(true);
        return saveResultVO;
    }

    @Override
    public void insertTableDataByArray(List<Map<String, Object>> tableDataList, FileListDTO fileList, List<FileItemDTO> fileItems) {
        String sqlTableName = "\"" + fileList.getFileName() + "\"";
        insertTableData(fileItems, sqlTableName, fileList.getPublicType(), tableDataList);
    }

    // 校验字符串是否为数值类型 最后的E\\d是为了兼容科学计数法
    private static final Pattern pattern = Pattern.compile("^-?\\d+(\\.\\d+)?(E\\d+)?$");
    @Override
    public List<String> checkDataValid(List<Map<String, Object>> tableDataList, FileListDTO fileList, List<FileItemDTO> fileItems) {
        List<String> errorList = new ArrayList<>();

        int count = 1;
        for (Map<String, Object> dataMap : tableDataList) {
            for (FileItemDTO fileItem : fileItems) {
                String fieldName = fileItem.getFieldName().toUpperCase();
                Object data = dataMap.get(fieldName);

                // 判断类型 主要判断数字类型的字段不能接收非数字内容
                if (data == null)
                    continue;

                if (fileItem.getFieldType() == FieldType.NUMBER) {
                    String dataStr;
//                    if (data instanceof BigDecimal) {
//                        // 使用toPlainString避免变成科学计数法
//                        dataStr = ((BigDecimal) data).toPlainString();
//                    } else if (data instanceof Double) {
//                        // 避免变成科学计数法
//                        dataStr = String.format("%.3f", (Double) data);
//                    } else {
                    dataStr = StringUtil.trimToEmpty(data.toString());
//                    }
//                  StringUtil.isNumeric
                    Matcher isNum = pattern.matcher(dataStr);
                    if (!isNum.matches()) {
                        errorList.add(String.format("第%s行“%s”字段格式错误，应为数字类型，实为“%s”", count, fileItem.getFieldDoc(), dataStr));
                    }
                }
            }
            count++;
        }
        return errorList;
    }

    private void insertTableDataByYearMonthZth(List<FileItemDTO> fileItems, Integer years, Integer months, Integer days, String ztId, String sno, String sqlTableName, PublicType publicType, List<Map<String, Object>> addArray, int fileItemSize) {
        // v1.0.7 item排序字段被更新为空问题 对item、itm这样的字段强制设值为行号（item这样的字段fileitem_cwgk的配置isShow=0 目前取不到 先不改）

//        String itemColumnName = null;
//        for (FileItemDTO fileItem : fileItems) {
//            if (StringUtil.equalsIgnoreCase(fileItem.getFieldName(), "item")) {
//                itemColumnName = "item";
//                break;
//            }
//            if (StringUtil.equalsIgnoreCase(fileItem.getFieldName(), "itm")) {
//                itemColumnName = "itm";
//                break;
//            }
//        }
//        int rowCount = 1;
        for (Map<String, Object> addMap : addArray) {
            addMap.put("YEARS", years);
            addMap.put("MONTHS", months);
            if (days != null) addMap.put("DAYS", days);
            addMap.put("ZTID", ztId);
            if (sno != null) addMap.put("SNO", sno);

//            // v1.0.7 item排序字段被更新为空问题 对item、itm这样的字段强制设值为行号
//            if (itemColumnName != null) {
//                addMap.put(itemColumnName.toUpperCase(), rowCount);
//            }
//            rowCount ++;
        }
        insertTableData(fileItems, sqlTableName, publicType, addArray);
    }

    private void insertTableData(List<FileItemDTO> fileItems, String sqlTableName, PublicType publicType, List<Map<String, Object>> addArray) {
        int fileItemSize = fileItems.size();

        // insert
        String fieldSql = getListTableDataFieldSql(fileItems, null, false);
        // 加上年月账套号字段
        fieldSql += ", years, zth";
        // 计算年月账套号共多少个字段个数
        int extraFieldCount = 2;
        switch (publicType) {
            case YEAR:
                break;
            case WEEK:
                fieldSql += ", months, days";
                extraFieldCount += 2;
                break;
            case TIMELY:
                fieldSql += ", months, sno";
                extraFieldCount += 2;
                break;
            default:
                fieldSql += ", months";
                extraFieldCount += 1;
                break;
        }

        StringBuilder sqlBuffer = new StringBuilder("INSERT INTO " + sqlTableName + "(" + fieldSql + ")");
        sqlBuffer.append("\nVALUES(");
        // 拼接 ?,?,?,?.....
        List<String> valueSqlList = new ArrayList<>();
        for (int i = 0; i < fileItemSize + extraFieldCount; i++) {
            valueSqlList.add("?");
        }
        sqlBuffer.append(StringUtil.join(valueSqlList, ","));
        sqlBuffer.append(")");


        jdbcTemplatePrimary.batchUpdate(sqlBuffer.toString(), new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> addMap = addArray.get(i);
                for (int j = 0; j < fileItemSize; j++) {
                    FileItemDTO fileItem = fileItems.get(j);
                    String fieldName = fileItem.getFieldName().toUpperCase();
                    ps.setObject(j+1, getDataAfterProcess(addMap.get(fieldName), fileItem));
                }
                // 年月账套号等字段
                // 20240205 改成从map中取值 兼容多年月多账套号的批量插入
                ps.setInt(fileItemSize + 1, parseInt(addMap.get("YEARS")));
                ps.setString(fileItemSize + 2, (String) addMap.get("ZTID"));
                switch (publicType) {
                    case YEAR:
                        break;
                    case WEEK:
                        ps.setInt(fileItemSize + 3, parseInt(addMap.get("MONTHS")));
                        ps.setInt(fileItemSize + 4, parseInt(addMap.get("DAYS")));
                        break;
                    case TIMELY:
                        ps.setInt(fileItemSize + 3, parseInt(addMap.get("MONTHS")));
                        ps.setString(fileItemSize + 4, (String) addMap.get("SNO"));
                        break;
                    default:
                        ps.setInt(fileItemSize + 3, parseInt(addMap.get("MONTHS")));
                        break;
                }
            }

            @Override
            public int getBatchSize() {
                return addArray.size();
            }
        });
    }

    // 转化Object值为int类型
    private int parseInt(Object value) {
        if (value == null) {
            return 0;
        }
        if (value instanceof Integer) {
            return (int) value;
        }
        if (value instanceof Number) {
            return ((Number) value).intValue();
        }
        return Integer.parseInt(value.toString());
    }

    // 保存时对存进数据库的字段内容进行特殊处理
    private Object getDataAfterProcess(Object value, FileItemDTO fileItem) {
        Object result = value;
        if (fileItem.getFieldType() == FieldType.NUMBER) {
            if (value != null && StringUtil.isEmpty(value.toString())) {
                // 数值类型 但传空字符串 保存时也保存为null
                result = null;
            }
        }

        // 数据加密（只加密字符串类型的字段）（这一段应该是放在处理完所有其他业务之后）
        if (fileItem.getIsEncrypt() &&
                (fileItem.getFieldType() == FieldType.CHAR || fileItem.getFieldType() == FieldType.MULTI_TEXT)) {
            if (value instanceof String) {
                result = encryptManager.encrypt((String) value);
            }
        }

        return result;
    }

    // 查询时对数据库的字段内容进行处理转义，如类型转换，解密等
    private Object readDataAfterProcess(Object value, FileItemDTO fileItem) {
        if (value == null) return null;
        String result = value.toString();
        // 数据加密（只加密字符串类型的字段）
        if (fileItem.getIsEncrypt() &&
                (fileItem.getFieldType() == FieldType.CHAR || fileItem.getFieldType() == FieldType.MULTI_TEXT)) {
            if (value instanceof String) {
                result = encryptManager.decrypt((String) value);
            }
        }

        // 处理日期格式 转成字符串
        if (value instanceof Timestamp) {
            // 去掉0时0分0秒
            result = StringUtil.replace(result, " 00:00:00.0", "");
        }

        // v1.0.3 增加处理&nbsp;等占位符
        result = HtmlUtils.htmlUnescape(result);

        return result;
    }

    // 获取查询公开数据内容语句的条件语句 年 月 账套号 根据公开类型判断
    private String getListTableDataSubSql(FileListDTO fileList) {
        String subSql;
        switch (fileList.getPublicType()) {
            case YEAR:
                // 年报的情况 不加月份条件
                subSql = " where years=:years and zth=:ztId";
                break;
//            case HALF_YEAR:
//                subSql = " where years=:years and months=:months and zth=:ztId";
//                break;
            case WEEK:
                subSql = " where years=:years and months=:months and days=:days and zth=:ztId";
                break;
            case TIMELY:
                // 支持使用sno查询
                subSql = " where years=:years and months=:months and zth=:ztId and sno=:sno";
                break;
            default:
                // 月报 季报 半年报都走这个条件
                subSql = " where years=:years and months=:months and zth=:ztId";
                break;
        }
        return subSql;
    }

    private Map<String, Object> getListTableDataParameters(FileListDTO fileList, TableDataQuery tableDataQuery) {
        Map<String, Object> result = new HashMap<>();
        result.put("years", tableDataQuery.getYears());
        result.put("ztId", tableDataQuery.getZtId());
        switch (fileList.getPublicType()) {
            case YEAR:
                // 年报的情况 不加月份条件
                break;
            case WEEK:
                result.put("months", tableDataQuery.getMonths());
                result.put("days", tableDataQuery.getDays());
                break;
            case TIMELY:
                // 及时公开 用sno查询
                result.put("months", tableDataQuery.getMonths());
                result.put("sno", tableDataQuery.getSno());
                break;
            default:
                // 月报 季报 半年报都走这个条件 需要再改
                result.put("months", tableDataQuery.getMonths());
                break;
        }
        return result;
    }

    // 本方法根据表头 返回C1,C2,C3这样的语句
    private static String getListTableDataFieldSql(List<FileItemDTO> fileItems, String idColumn, boolean isQuery) {
        // 这里直接把字段名称全部转成大写
        List<String> fileItemCollect = ListUtil.toSingleList(fileItems,
                fileItem -> {
                    if (isQuery) {
                        // 查询的情况 要给字段指定别名 C1 as C1, C2 as C2...
                        return fileItem.getFieldName() + " as " + StringUtil.upperCase(fileItem.getFieldName());
                    }
                    // insert语句的情况 只要字段名
                    return StringUtil.upperCase(fileItem.getFieldName());
                });
        // 加上主键字段
        if (idColumn != null) {
            fileItemCollect.add(idColumn + " as ID");
        }
        return StringUtil.join(fileItemCollect, ",");
    }
}
